package br.com.werp.regras.suporte.integracao;

import br.com.werp.regras.geral.Debuger;
import br.com.werp.regras.geral.ErroPadrao;
import br.com.werp.regras.geral.integracao.MasterDAO;
import br.com.werp.regras.geral.util.Constantes;
import br.com.werp.regras.geral.util.Util;
import br.com.werp.regras.geral.vos.suporte.AplicativoVO;
import br.com.werp.regras.geral.vos.suporte.HdVO;
import br.com.werp.regras.geral.vos.suporte.RedeMaquinaVO;
import br.com.werp.regras.geral.vos.suporte.SoVO;
import br.com.werp.regras.geral.vos.suporte.VideoVO;
import br.com.werp.regras.geral.vos.tabelas.Aplicativo_maquina;
import br.com.werp.regras.geral.vos.tabelas.Hd_maquina;
import br.com.werp.regras.geral.vos.tabelas.Maq_video;
import br.com.werp.regras.geral.vos.tabelas.Maquina;
import br.com.werp.regras.geral.vos.tabelas.Rede;
import br.com.werp.regras.geral.vos.tabelas.Rede_maquina;
import br.com.werp.regras.geral.vos.tabelas.So_maquina;
import br.com.werp.regras.geral.vos.tabelas.Usuario_maquina;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.ResourceBundle;


/**
 * @author Candido
 * Created on 30/09/2004
 */
public class MaquinaDAO extends MasterDAO {
    private static final String OBTER_PLACAS_VIDEO_MAQUINA = "OBTER_PLACAS_VIDEO_MAQUINA";
    private static final String VIDO_ID = "VIDO_ID";
    private static final String MQVD_ONBOARD = "MQVD_ONBOARD";
    private static final String VIDO_MARCA = "VIDO_MARCA";
    private static final String VIDO_MODELO = "VIDO_MODELO";
    private static final String VIDO_SLOT = "VIDO_SLOT";
    private static final String VIDO_TXTRANSF = "VIDO_TXTRANSF";
    private static final String INSERIR_PLACAS_VIDEO_MAQUINA = "INSERIR_PLACAS_VIDEO_MAQUINA";
    private static final String OBTER_PLACAS_VIDEO_MAQUINA_ONBOARD = "OBTER_PLACAS_VIDEO_MAQUINA_ONBOARD";
    private static final String REMOVER_PLACAS_VIDEO_MAQUINA = "REMOVER_PLACAS_VIDEO_MAQUINA";
    private static final String OBTER_HD_MAQUINA = "OBTER_HD_MAQUINA";
    private static final String HADK_ID = "HADK_ID";
    private static final String HADK_MARCA = "HADK_MARCA";
    private static final String HADK_MODELO = "HADK_MODELO";
    private static final String APIC_ID = "APLC_ID";
    private static final String APLC_NOME = "APLC_NOME";
    private static final String APLC_VERSAO = "APLC_VERSAO";
    private static final String REMOVER_APLICATIVO_MAQUINA = "REMOVER_APLICATIVO_MAQUINA";
    private static final String INSERIR_APLICATIVO_MAQUINA = "INSERIR_APLICATIVO_MAQUINA";
    private static final String OBTER_APLICATIVO_MAQUINA = "OBTER_APLICATIVO_MAQUINA";
    private static final String OBTER_PLACAS_REDE_MAQUINA = "OBTER_PLACAS_REDE_MAQUINA";
    private static final String REMOVER_HDS_MAQUINA = "REMOVER_HDS_MAQUINA";
    private static final String REMOVER_PLACAS_REDE_MAQUINA = "REMOVER_PLACAS_REDE_MAQUINA";
    private static final String REDE_ID = "REDE_ID";
    private static final String REDE_MARCA = "REDE_MARCA";
    private static final String REDE_MODELO = "REDE_MODELO";
    private static final String REMQ_PP = "REMQ_PP";
    private static final String REMQ_SW = "REMQ_SW";
    private static final String REMQ_IP = "REMQ_IP";
    private static final String REMQ_ONBOARD = "REMQ_ONBOARD";
    private static final String OBTER_SO_MAQUINA = "OBTER_SO_MAQUINA";
    private static final String STOP_ID = "STOP_ID";
    private static final String STOP_NOME = "STOP_NOME";
    private static final String STOP_VERSAO = "STOP_VERSAO";
    private static final String REMOVER_SO_MAQUINA = "REMOVER_SO_MAQUINA";
    private static MaquinaDAO maquinaDAO;

    /**
         * @param banco
         * @throws Exception
         */
    private MaquinaDAO(String banco) throws Exception {
        super(banco);
    }

    public static MaquinaDAO getMaquinaDAO() throws Exception {
        if (maquinaDAO == null) {
            ResourceBundle bundle = ResourceBundle.getBundle(Constantes.CONFIG);
            maquinaDAO = new MaquinaDAO(bundle.getString(Constantes.BANCO));
        }

        return maquinaDAO;
    }

    public List obterPlacasVideoMaquina(int maquinaId)
        throws Exception {
        List list = null;
        Connection conn = null;

        try {
            String query = getQuery(OBTER_PLACAS_VIDEO_MAQUINA);
            conn = getConexao();

            PreparedStatement statement = conn.prepareStatement(query);
            setInt(1, statement, maquinaId);

            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList();

            while (resultSet.next()) {
                VideoVO videoVO = new VideoVO();
                videoVO.setVido_id(resultSet.getInt(VIDO_ID));

                if ("s".equalsIgnoreCase(resultSet.getString(MQVD_ONBOARD))) {
                    videoVO.setMqvd_onboard(true);
                } else {
                    videoVO.setMqvd_onboard(false);
                }

                videoVO.setVido_marca(resultSet.getString(VIDO_MARCA));
                videoVO.setVido_modelo(resultSet.getString(VIDO_MODELO));
                videoVO.setVido_slot(resultSet.getString(VIDO_SLOT));
                videoVO.setVido_txtransf(resultSet.getString(VIDO_TXTRANSF));
                list.add(videoVO);
            }

            return list;
        } catch (Exception e) {
            throw e;
        } finally {
            liberaConeccao(conn);
        }
    }

    public void inserirPlacaVideo(Maq_video maq_video, Object key)
        throws Exception {
        Connection conn = null;
        conn = validarTransacao(key);
        inserirVO(conn, maq_video);
    }

    public boolean placaVideoOboard(int maquinaId) throws Exception {
        Connection conn = null;

        try {
            String query = getQuery(OBTER_PLACAS_VIDEO_MAQUINA_ONBOARD);
            conn = getConexao();

            PreparedStatement statement = conn.prepareStatement(query);
            setInt(1, statement, maquinaId);

            ResultSet resultSet = statement.executeQuery();

            return resultSet.next();
        } catch (Exception e) {
            throw e;
        } finally {
            liberaConeccao(conn);
        }
    }

    public static void main(String[] args) throws Exception {
        //FabricaDAO.getMaquinaDAO().inserirPlacaVideo(2,1,"n");
        System.out.println(FabricaDAO.getMaquinaDAO().obterHdMaquina(1));
    }

    /**
     * @param videoVO
     * @param maquinaId
     * @throws Exception
     */
    public void removerPlacaVideo(VideoVO videoVO, String maquinaId, Object key)
        throws Exception {
        Connection conn = null;
        String query = getQuery(REMOVER_PLACAS_VIDEO_MAQUINA);
        conn = validarTransacao(key);

        PreparedStatement statement = conn.prepareStatement(query);
        setInt(1, statement, Integer.parseInt(maquinaId));
        setInt(2, statement, videoVO.getVido_id());
        statement.executeUpdate();
    }

    /**
     * @param i
     * @return
     * @throws Exception
     */
    public List obterHdMaquina(int maquinaId) throws Exception {
        List list = null;
        Connection conn = null;

        try {
            String query = getQuery(OBTER_HD_MAQUINA);
            conn = getConexao();

            PreparedStatement statement = conn.prepareStatement(query);
            setInt(1, statement, maquinaId);

            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList();

            while (resultSet.next()) {
                HdVO hdVO = new HdVO();
                hdVO.setHadk_id(resultSet.getInt(HADK_ID));
                hdVO.setHadk_marca(resultSet.getString(HADK_MARCA));
                hdVO.setHadk_modelo(resultSet.getString(HADK_MODELO));
                list.add(hdVO);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            liberaConeccao(conn);
        }

        return list;
    }

    /**
     * @param maquinaId
     * @return
     * @throws Exception
     */
    public List obterAplicativos(int maquinaId) throws Exception {
        List list = null;
        Connection conn = null;

        try {
            String query = getQuery(OBTER_APLICATIVO_MAQUINA);
            conn = getConexao();

            PreparedStatement statement = conn.prepareStatement(query);
            setInt(1, statement, maquinaId);

            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList();

            while (resultSet.next()) {
                AplicativoVO aplicativoVO = new AplicativoVO();
                aplicativoVO.setAplc_id(resultSet.getInt(APIC_ID));
                aplicativoVO.setAplc_nome(resultSet.getString(APLC_NOME));
                aplicativoVO.setAplc_versao(resultSet.getString(APLC_VERSAO));
                list.add(aplicativoVO);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            liberaConeccao(conn);
        }

        return list;
    }

    /**
     * @param aplicativoVO
     * @param maquinaId
     * @throws Exception
     */
    public void removerAplicativos(AplicativoVO aplicativoVO, String maquinaId,
        Object key) throws Exception {
        Connection conn = null;
        String query = getQuery(REMOVER_APLICATIVO_MAQUINA);
        conn = validarTransacao(key);

        PreparedStatement statement = conn.prepareStatement(query);
        setInt(1, statement, Integer.parseInt(maquinaId));
        setInt(2, statement, aplicativoVO.getAplc_id());
        statement.executeUpdate();
    }

    /**
     * @param aplicativoVO
     * @param maquinaId
     * @throws Exception
     */
    public void inserirAplicativo(Aplicativo_maquina aplicativo_maquina,
        Object key) throws Exception {
        Connection conn = null;
        conn = validarTransacao(key);
        inserirVO(conn, aplicativo_maquina);
    }

    /**
     * @param maquinaId
     * @return
     * @throws Exception
     */
    public List obterPlacasRedeMaquina(int maquinaId) throws Exception {
        List list = null;
        Connection conn = getConexao();

        try {
            String query = getQuery(OBTER_PLACAS_REDE_MAQUINA);
            PreparedStatement statement = conn.prepareStatement(query);
            setInt(1, statement, maquinaId);

            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList();

            while (resultSet.next()) {
                RedeMaquinaVO redeMaquinaVO = new RedeMaquinaVO();
                redeMaquinaVO.setRede_id(resultSet.getInt(REDE_ID));
                redeMaquinaVO.setRede_marca(resultSet.getString(REDE_MARCA));
                redeMaquinaVO.setRede_modelo(resultSet.getString(REDE_MODELO));
                redeMaquinaVO.setRemq_pp(resultSet.getString(REMQ_PP));
                redeMaquinaVO.setRemq_sw(resultSet.getString(REMQ_SW));
                redeMaquinaVO.setRemq_ip(resultSet.getString(REMQ_IP));
                redeMaquinaVO.setRemq_id(redeMaquinaVO.getRede_id());

                String onboard = resultSet.getString(REMQ_ONBOARD);

                redeMaquinaVO.setRemq_onboard("S".equals(onboard) ? true : false);
                list.add(redeMaquinaVO);
            }

            return list;
        } catch (Exception e) {
            throw e;
        } finally {
            liberaConeccao(conn);
        }
    }

    /**
     * @param hdVO
     * @param maquinaId
     * @throws Exception
     */
    public void removerHds(HdVO hdVO, String maquinaId, Object key)
        throws Exception {
        Connection conn = null;
        String query = getQuery(REMOVER_HDS_MAQUINA);
        conn = validarTransacao(key);

        PreparedStatement statement = conn.prepareStatement(query);
        setInt(1, statement, Integer.parseInt(maquinaId));
        setInt(2, statement, hdVO.getHadk_id());

        statement.executeUpdate();
    }

    /**
     * @param hd_maquina
     * @param key
     * @throws Exception
     */
    public void inserirHds(Hd_maquina hd_maquina, Object key)
        throws Exception {
        Connection conn = null;
        conn = validarTransacao(key);
        inserirVO(conn, hd_maquina);
    }

    /**
     * @param redeMaquinaVO
     * @param maquinaId
     * @throws Exception
     */
    public void removerPlacaRede(RedeMaquinaVO redeMaquinaVO, String maquinaId,
        Object key) throws Exception {
        Connection conn = null;
        String query = getQuery(REMOVER_PLACAS_REDE_MAQUINA);
        conn = validarTransacao(key);

        PreparedStatement statement = conn.prepareStatement(query);
        setInt(1, statement, Integer.parseInt(maquinaId));
        setInt(2, statement, redeMaquinaVO.getRemq_id());
        statement.executeUpdate();
    }

    /**
     * @param redeMaquinaVO
     * @param maquinaId
     * @throws Exception
     */
    public void inserirPlacaRede(Rede_maquina rede_maquina, Object key)
        throws Exception {
        Connection conn = null;
        conn = validarTransacao(key);
        inserirVO(conn, rede_maquina);
    }

    public Rede obterPlacasRede(int redeId) throws Exception {
        List list = null;
        Connection conn = getConexao();

        try {
            String where = "rede_id = " + redeId;

            list = preencheVO(conn, new Rede(), where);

            return (Rede) list.get(0);
        } catch (Exception e) {
            throw e;
        } finally {
            liberaConeccao(conn);
        }
    }

    /**
     * @param maquinaId
     * @return
     * @throws Exception
     */
    public List obterSo(int maquinaId) throws Exception {
        List list = null;
        Connection conn = null;

        try {
            String query = getQuery(OBTER_SO_MAQUINA);
            conn = getConexao();

            PreparedStatement statement = conn.prepareStatement(query);
            setInt(1, statement, maquinaId);

            ResultSet resultSet = statement.executeQuery();
            list = new ArrayList();

            while (resultSet.next()) {
                SoVO soVO = new SoVO();
                soVO.setStop_id(resultSet.getInt(STOP_ID));
                soVO.setStop_nome(resultSet.getString(STOP_NOME));
                soVO.setStop_versao(resultSet.getString(STOP_VERSAO));
                list.add(soVO);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            liberaConeccao(conn);
        }

        return list;
    }

    /**
     * @param soVO
     * @param maquinaId
     * @param key
     * @throws Exception
     */
    public void removerSo(SoVO soVO, String maquinaId, Object key)
        throws Exception {
        Connection conn = null;
        String query = getQuery(REMOVER_SO_MAQUINA);
        conn = validarTransacao(key);

        PreparedStatement statement = conn.prepareStatement(query);
        setInt(1, statement, Integer.parseInt(maquinaId));
        setInt(2, statement, soVO.getStop_id());
        Debuger.debugar(statement, getClass());
        statement.executeUpdate();
    }

    /**
     * @param so_maquina
     * @param key
     * @throws Exception
     */
    public void inserirSo(So_maquina so_maquina, Object key)
        throws Exception {
        Connection conn = null;
        conn = validarTransacao(key);
        inserirVO(conn, so_maquina);
    }

    public void inserirMaquina(Object key, Maquina maquina)
        throws Exception {
        Connection conn = validarTransacao(key);
        inserirVO(conn, maquina, new String[] { "maqn_id" });
    }

    public Maquina obterTipoMemoria(String maquina) throws Exception {
        List list = null;
        Connection conn = getConexao();

        try {
            String where = "maqn_id = " + Integer.parseInt(maquina);

            list = preencheVO(conn, new Maquina(), where);

            return (Maquina) Util.get0(list);
        } catch (Exception e) {
            throw e;
        } finally {
            liberaConeccao(conn);
        }
    }

    public void manterTipoMemoria(Object key, Maquina maquina)
        throws Exception {
        Connection connection = validarTransacao(key);

        String[] pks = new String[] { "maqn_id" };

        atualizarVO(connection, maquina, pks,
            Arrays.asList(new String[] { "maqn_memoria_tipo" }));
    }

    public List obterMaquina(Maquina maquina) throws Exception {
        Connection conn = getConexao();

        try {
            String where = "";

            if (!Util.isNullOrEmpty(maquina.getMaqn_nome())) {
                where += (" and maqn_nome like " +
                aspasLike(maquina.getMaqn_nome()));
                maquina.setMaqn_nome(null);
            }

            return pesquisarVO(conn, maquina, where);
        } finally {
            liberaConeccao(conn);
        }
    }

    public void atualizarMaquina(Object key, Maquina maquina)
        throws Exception {
        Connection connection = validarTransacao(key);
        atualizarVO(connection, maquina, new String[] { "maqn_id" });
    }

    public void excluirRedeMaquina(Rede_maquina rede_maquina, Object key)
        throws Exception {
        Connection connection = validarTransacao(key);
        excluirVO(connection, rede_maquina);
    }

    public void excluirVideoMaquina(Maq_video maq_video, Object key)
        throws Exception {
        Connection connection = validarTransacao(key);
        excluirVO(connection, maq_video);
    }

    public void excluirSOMaquina(So_maquina so_maquina, Object key)
        throws Exception {
        Connection connection = validarTransacao(key);
        excluirVO(connection, so_maquina);
    }

    public void excluirAplicativoMaquina(
        Aplicativo_maquina aplicativo_maquina, Object key)
        throws Exception {
        Connection connection = validarTransacao(key);
        excluirVO(connection, aplicativo_maquina);
    }

    public void excluirUsuarioMaquina(Usuario_maquina usuario_maquina,
        Object key) throws Exception {
        Connection connection = validarTransacao(key);
        excluirVO(connection, usuario_maquina);
    }

    public void excluirHDMaquina(Hd_maquina hd_maquina, Object key)
        throws Exception {
        Connection connection = validarTransacao(key);
        excluirVO(connection, hd_maquina);
    }

    public void excluirMaquina(Maquina maquina, Object key)
        throws Exception {
        Connection connection = validarTransacao(key);
        excluirVO(connection, maquina);
    }
}
